package com.wordpress.minhnvt1.hrm.dba;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.SQLException;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.util.Log;

public class DBAdapter {
	//table HumanResource
	public static final String HR_ID = "hrId";
	public static final String HR_NAME = "hrName";
	public static final String HR_PASS = "hrPass";
	public static final String HR_EMAIL = "hrEmail";
	
	//table Candidate
	public static final String CA_ID = "caId";
	public static final String CA_NAME = "caName";
	public static final String CA_AGE = "caAge";
	public static final String CA_EMAIL = "caEmail";
	public static final String CA_ADDRESS = "caAddress";
	public static final String CA_POSITION = "caPosition";
	public static final String CA_HR = "caHr";
	
	private static final String TAG = "DBAdapter";
	
	private static final String DB_NAME = "HRM";
	private static final String DB_TABLE_HR = "HumanResource";
	private static final String DB_TABLE_CA = "Candidates";
	private static final int DB_VERSION = 2;
	
	private static final String DB_CREATE_HR =
	        "create table HumanResource (hrId integer primary key autoincrement, "
	        + "hrName text, "
	        + "hrPass text, "
	        + "hrEmail text);";
	private static final String DB_CREATE_CA =
	        "create table Candidates (caId integer primary key autoincrement, "
	        + "caName text, "
	        + "caAge text, "
	        + "caEmail text, "
	        + "caAddress text, "
	        + "caPosition text, "
	        + "caHr text);";
	    
	private final Context _context;
	private SQLiteDatabase db;
    private DatabaseHelper DBHelper;
    
    public DBAdapter(Context context) {
    	this._context = context;
    }
	private static class DatabaseHelper extends SQLiteOpenHelper {

		public DatabaseHelper(Context context) {
			super(context, DB_NAME, null, DB_VERSION);
			
		}

		@Override
		public void onCreate(SQLiteDatabase db) {
			try {
				db.execSQL(DB_CREATE_HR);
				db.execSQL(DB_CREATE_CA);
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}

		@Override
		public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
			Log.w(TAG, "Updating DB....");
			db.execSQL("DROP TABLE IF EXISTS HumanResource");
            db.execSQL("DROP TABLE IF EXISTS Candidates");
            onCreate(db);
		}
		
	}
	//open connection to database
	public DBAdapter openDB() throws SQLException {
		DBHelper = new DatabaseHelper(_context);
		db = DBHelper.getWritableDatabase();
		return this;
	}
	//close connect
	public void closeDB() {
		Log.w(TAG, "DB is closing...");
		db.close();
	}
	
	//----------------------------- HUMANRESOURCE TABLE --------------
	public long insertHumanResource(String name, String pass, String email) {
		ContentValues cv = new ContentValues();
		cv.put(HR_NAME, name);
		cv.put(HR_PASS, pass);
		cv.put(HR_EMAIL, email);
		return db.insert(DB_TABLE_HR, null, cv);
	}
	
	public boolean deleteHumanResource(long id) {
		return db.delete(DB_TABLE_HR, HR_ID + " = " + id, null) > 0;
	}
	
	public boolean updateHumanRsource(long id, String name, String pass, String email) {
		ContentValues cv = new ContentValues();
		cv.put(HR_ID, id);
		cv.put(HR_NAME, name);
		cv.put(HR_PASS, pass);
		cv.put(HR_EMAIL, email);
		return db.update(DB_TABLE_HR, cv, HR_ID + " = " + id, null) > 0;
	}
	
	public Cursor getAllHumanResource() {
		return db.query(DB_TABLE_HR, 
					new String[] {HR_ID, HR_NAME, HR_PASS, HR_EMAIL}, 
					null, null, null, null, null);
	}
	public Cursor getHumanResourceById(long id) {
		Cursor c = db.query(DB_TABLE_HR, 
						new String[] {HR_ID, HR_NAME, HR_PASS, HR_EMAIL}, 
						HR_ID + " = " + id, 
						null, null, null, null);
		if (c != null) {
			c.moveToFirst();
		}
		return c;
	}
	
	public Cursor getHumanResourceByName(String name) {
		Cursor c = db.query(DB_TABLE_HR, 
					new String[] {HR_ID, HR_NAME, HR_PASS, HR_EMAIL}, 
					HR_NAME + " ='" + name + "'", 
					null, null, null, null);
		if (c != null) {
			c.moveToFirst();
		}
		return c;
	}
	
	public boolean checkLogin(String name, String pass) {
		Cursor c = db.rawQuery("SELECT * FROM " + DB_TABLE_HR + " WHERE " + HR_NAME + " = ? AND " + HR_PASS + " = ? ", 
						new String[] {name, pass});
		if (c != null) {
			if (c.getCount() > 0) {
				return true;
			}
			
		}
		return false;
	}
	
	//-------------------------------- CANDIDATES --------------------------
	public long insertCandidate(String name, String age, String email, String address, String position, String hr) {
		ContentValues cv = new ContentValues();
		
		cv.put(CA_NAME, name);
		cv.put(CA_AGE, age);
		cv.put(CA_EMAIL, email);
		cv.put(CA_ADDRESS, address);
		cv.put(CA_POSITION, position);
		cv.put(CA_HR, hr);
		
		return db.insert(DB_TABLE_CA, null, cv);
		
	}
	public boolean deleteCandidate(long id) {
		return db.delete(DB_TABLE_CA, CA_ID + " = " + id, null) > 0;
	}
	public boolean updateCandidate(long id, String name, String age, String email, String address, String position, String hr) {
		ContentValues cv = new ContentValues();
		
		cv.put(CA_ID, id);
		cv.put(CA_NAME, name);
		cv.put(CA_AGE, age);
		cv.put(CA_EMAIL, email);
		cv.put(CA_ADDRESS, address);
		cv.put(CA_POSITION, position);
		cv.put(CA_HR, hr);
		
		return db.update(DB_TABLE_CA, cv, CA_ID + " = " + id,  null) > 0; 
	}
	public Cursor getAllCandidate() {
		return db.query(DB_TABLE_CA, 
					new String[] {CA_NAME, CA_AGE, CA_EMAIL, CA_ADDRESS, CA_POSITION, CA_HR}, 
					null, null, null, null, null);
	}
	public Cursor getCandidateById(long id) {
		Cursor c = db.query(DB_TABLE_CA, 
						new String[] {CA_NAME, CA_AGE, CA_EMAIL, CA_ADDRESS, CA_POSITION, CA_HR}, 
						CA_ID + " = " + id, 
						null, null, null, null);
		if (c !=null) {
			c.moveToFirst();
		}
		return c;
	}
	
	public Cursor getCandidateByHR(String hr) throws SQLException {
		Cursor c = db.query(DB_TABLE_CA, 
					new String[] {CA_ID, CA_NAME, CA_AGE, CA_EMAIL, CA_ADDRESS, CA_POSITION, CA_HR}, 
					CA_HR + " = " + hr, 
					null, null, null, null);
		if (c != null) {
			c.moveToFirst();
		}
		return c;
	}
	public Cursor getCandidateByName(String name) throws SQLException {
		Cursor c = db.query(DB_TABLE_CA, 
					new String[] {CA_ID, CA_NAME, CA_AGE, CA_EMAIL, CA_ADDRESS, CA_POSITION, CA_HR}, 
					CA_NAME + " = " + name, 
					null, null, null, null);
		if (c != null) {
			c.moveToFirst();
		}
		return c;
	}
	public Cursor getCandidateByAge(String age) throws SQLException {
		Cursor c = db.query(DB_TABLE_CA, 
					new String[] {CA_ID, CA_NAME, CA_AGE, CA_EMAIL, CA_ADDRESS, CA_POSITION, CA_HR}, 
					CA_AGE + " = " + age, 
					null, null, null, null);
		if (c != null) {
			c.moveToFirst();
		}
		return c;
	}
	public Cursor getCandidateByPosition(String position) throws SQLException {
		Cursor c = db.query(DB_TABLE_CA, 
					new String[] {CA_ID, CA_NAME, CA_AGE, CA_EMAIL, CA_ADDRESS, CA_POSITION, CA_HR}, 
					CA_POSITION + " = " + position, 
					null, null, null, null);
		if (c != null) {
			c.moveToFirst();
		}
		return c;
	}
}
